﻿<%@ WebHandler Language="C#" Class="ContainerStepPlanTrendBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 准时完成率趋势看板
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.2.10
 */

public class ContainerStepPlanTrendBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    TreeObject t = new TreeObject();
    List<TreeObject> treeNodes = new List<TreeObject>();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getBarData":
                        r = getBarData(jObject);//柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getBarChartDetail":
                        r = getBarChartDetail(jObject);//柱状图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportBarChartDetail":
                        exportBarChartDetail(jObject);//柱状图明细导出
                        break;
                    case "getLineChartData":
                        r = getLineChartData(jObject);//折线图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getLineChartDetail":
                        r = getLineChartDetail(jObject);//折现图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportLineChartDetail":
                        exportLineChartDetail(jObject);//折现图明细导出
                        break;
                    case "getWorkerTrend":
                        r = getWorkerTrend(jObject);//工人折线图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getWorkerTrendDetail":
                        r = getWorkerTrendDetail(jObject);//工人折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportWorkerLineChartDetail":
                        exportWorkerLineChartDetail(jObject);//工人折线图明细导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    // 均值及标准差
    Result getBarData(JObject jObject)
    {
        string sql = @"
SELECT {0},ROUND(STDDEV(t.delaydays),2) std_val,ROUND(AVG(t.delaydays),2) avg_val
FROM (
  SELECT csp.containername,csp.workflowstepname,csp.plannedfinishdate,csp.checkdate,
         csr.factoryid,csp.factoryname,
         csr.workcenterid,w.workcentername,
         csr.teamid,t.teamname,
         csr.reportemployeename fullname,
         TRUNC(csp.checkdate) - TRUNC(csp.plannedfinishdate) delaydays
  FROM r_containerstepplanboard@mesreport csp
  LEFT JOIN containersteprecord csr ON csr.containerid = csp.containerid AND csr.workflowstepname = csp.workflowstepname
  LEFT JOIN workcenter w ON w.workcenterid = csr.workcenterid
  LEFT JOIN team t ON t.teamid = csr.teamid
  WHERE csp.status > 0 AND csr.teamid IS NOT NULL AND csr.reportemployeename IS NOT NULL AND csp.factorycode <> '602'";

        //计划完成时间
        if (jObject["plannedfinishdate"] != null && jObject["plannedfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedfinishdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) >= to_date('{0}','yyyy/mm/dd') AND TRUNC(csp.plannedfinishdate) <= to_date('{1}','yyyy/mm/dd')",
                                 arr[0].ToString(), arr[1].ToString());
        }

        //完工时间
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND csp.checkdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss') AND csp.checkdate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')",
                                 arr[0].ToString(), arr[1].ToString());
        }

        if (jObject["selectOrg"] != null && jObject["selectOrg"].ToString() != "[]")
        {
            JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
            if (selectOrg.Count == 1)
            {
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
                sql += @" ) t GROUP BY t.workcentername";
                sql = string.Format(sql, "t.workcentername item");
            }
            else if (selectOrg.Count == 2)
            {
                sql += string.Format(" AND csr.factoryid = '{0}' AND csr.workcenterid = '{1}'", selectOrg[0].ToString(), selectOrg[1].ToString());
                sql += @" ) t GROUP BY t.teamname";
                sql = string.Format(sql, "t.teamname item");
            }
            else
            {
                sql += string.Format(" AND csr.factoryid = '{0}' AND csr.workcenterid = '{1}' AND csr.teamid = '{2}'",
                                    selectOrg[0].ToString(), selectOrg[1].ToString(), selectOrg[2].ToString());
                sql += @" ) t GROUP BY t.fullname";
                sql = string.Format(sql, "t.fullname item");
            }
        }
        else
        {
            sql += @" ) t GROUP BY t.factoryname";
            sql = string.Format(sql, "t.factoryname item");
        }
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //折线图
    Result getLineChartData(JObject jObject)
    {
        string sql = @"
SELECT {0}
       ROUND(STDDEV(t.delaydays),2) std_val,ROUND(AVG(t.delaydays),2) avg_val
FROM
(
  SELECT csp.containername,csp.workflowstepname,csp.plannedfinishdate,TRUNC(csp.checkdate) checkdate,
         TRUNC(csp.checkdate) - TRUNC(csp.plannedfinishdate) delaydays,
         csp.status,
         to_char(csp.checkdate,'yyyy') || '年' years,
         to_char(csp.checkdate,'mm') || '月' monthes,
         to_char(csp.checkdate,'ww') || '周' weeks
  FROM r_containerstepplanboard@mesreport csp
  LEFT JOIN containersteprecord csr ON csr.containerid = csp.containerid AND csr.workflowstepname = csp.workflowstepname
  WHERE csp.status > 0 AND csr.teamid IS NOT NULL AND csr.reportemployeename IS NOT NULL AND csp.factorycode <> '602'";

        //计划完成时间
        if (jObject["plannedfinishdate"] != null && jObject["plannedfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedfinishdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) >= to_date('{0}','yyyy/mm/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) <= to_date('{0}','yyyy/mm/dd')", arr[1].ToString());
        }

        //完工时间
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND csp.checkdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND csp.checkdate <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", arr[1].ToString());
        }

        //所选组织
        if (jObject["selectOrg"] != null && jObject["selectOrg"].ToString() != "[]")
        {
            JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
            if (selectOrg.Count == 1)
            {
                //指定分厂
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
            }
            else if (selectOrg.Count == 2)
            {
                //指定：分厂/工区
                sql += string.Format(" AND csr.factoryid = '{0}' AND csr.workcenterid = '{1}'", selectOrg[0].ToString(), selectOrg[1].ToString());
            }
            else
            {
                //指定：分厂/工区/班组
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
                sql += string.Format(" AND csr.workcenterid = '{0}'", selectOrg[1].ToString());
                sql += string.Format(" AND csr.teamid = '{0}'", selectOrg[2].ToString());
            }
        }

        string trendType = jObject["trendType"].ToString();
        //颗粒度
        if (trendType == "月")
        {
            //按月
            sql = string.Format(sql, "t.years || t.monthes item,");
            sql += @"
) t
GROUP BY t.years,t.monthes
ORDER BY t.years,t.monthes";
        }
        else if (trendType == "周")
        {
            //按周
            sql = string.Format(sql, "t.years || t.weeks item,");
            sql += @"
) t
GROUP BY t.years,t.weeks
ORDER BY t.years,t.weeks";
        }
        else
        {
            //按日
            sql = string.Format(sql, "to_char(t.checkdate,'yyyy-mm-dd') item,");
            sql += @"
) t
GROUP BY t.checkdate
ORDER BY t.checkdate";
        }
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工人均值及标准差趋势
    Result getWorkerTrend(JObject jObject)
    {
        string sql = @"
SELECT {0}
       ROUND(STDDEV(t.delaydays),2) std_val,ROUND(AVG(t.delaydays),2) avg_val
FROM
(
  SELECT csp.containername,csp.workflowstepname,csp.plannedfinishdate,TRUNC(csp.checkdate) checkdate,
         TRUNC(csp.checkdate) - TRUNC(csp.plannedfinishdate) delaydays,
         csp.status,
         to_char(csp.checkdate,'yyyy') || '年' years,
         to_char(csp.checkdate,'mm') || '月' monthes,
         to_char(csp.checkdate,'ww') || '周' weeks
  FROM r_containerstepplanboard@mesreport csp
  LEFT JOIN containersteprecord csr ON csr.containerid = csp.containerid AND csr.workflowstepname = csp.workflowstepname
  WHERE csp.status > 0 AND csr.teamid IS NOT NULL AND csr.reportemployeename IS NOT NULL AND csp.factorycode <> '602'";

        //计划完成时间
        if (jObject["plannedfinishdate"] != null && jObject["plannedfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedfinishdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) >= to_date('{0}','yyyy/mm/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) <= to_date('{0}','yyyy/mm/dd')", arr[1].ToString());
        }

        //完工时间
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND csp.checkdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND csp.checkdate <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", arr[1].ToString());
        }

        //工人
        if (jObject["worker"] != null && jObject["worker"].ToString() != "")
        {
            sql += string.Format(" AND csr.reportemployeename = '{0}'", jObject["worker"].ToString());
        }

        //所选组织
        if (jObject["selectOrg"] != null && jObject["selectOrg"].ToString() != "[]")
        {
            JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
            if (selectOrg.Count == 1)
            {
                //指定分厂
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
            }
            else if (selectOrg.Count == 2)
            {
                //指定：分厂/工区
                sql += string.Format(" AND csr.factoryid = '{0}' AND csr.workcenterid = '{1}'", selectOrg[0].ToString(), selectOrg[1].ToString());
            }
            else
            {
                //指定：分厂/工区/班组
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
                sql += string.Format(" AND csr.workcenterid = '{0}'", selectOrg[1].ToString());
                sql += string.Format(" AND csr.teamid = '{0}'", selectOrg[2].ToString());
            }
        }

        string trendType = jObject["trendType"].ToString();
        //颗粒度
        if (trendType == "月")
        {
            //按月
            sql = string.Format(sql, "t.years || t.monthes item,");
            sql += @"
) t
GROUP BY t.years,t.monthes
ORDER BY t.years,t.monthes";
        }
        else if (trendType == "周")
        {
            //按周
            sql = string.Format(sql, "t.years || t.weeks item,");
            sql += @"
) t
GROUP BY t.years,t.weeks
ORDER BY t.years,t.weeks";
        }
        else
        {
            //按日
            sql = string.Format(sql, "to_char(t.checkdate,'yyyy-mm-dd') item,");
            sql += @"
) t
GROUP BY t.checkdate
ORDER BY t.checkdate";
        }
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //柱状图明细公用sql
    string getBarChartDetailSql(JObject jObject)
    {
        string sql = @"
SELECT csp.factoryname,csr.workcentername,csr.teamname,csr.reportemployeename,csp.containername,csp.workflowstepname,csp.plannedfinishdate,csp.checkdate,
       TRUNC(csp.checkdate) - TRUNC(csp.plannedfinishdate) delaydays,
       DECODE(csp.status,1,'按时完成',2,'超期完成') status,
       cp.stepplancreatedate
FROM r_containerstepplanboard@mesreport csp
INNER JOIN containerstepplan cp ON cp.containerid = csp.containerid AND cp.workflowstepname = csp.workflowstepname AND cp.plantype = 1
INNER JOIN containersteprecord csr ON csr.containerid = csp.containerid AND csr.workflowstepname = csp.workflowstepname
WHERE csp.status > 0 AND csr.teamid IS NOT NULL AND csr.reportemployeename IS NOT NULL AND csp.factorycode <> '602'";
        string paramsName = jObject["paramsName"].ToString();
        //所选组织
        if (jObject["selectOrg"] != null && jObject["selectOrg"].ToString() != "[]")
        {
            JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
            if (selectOrg.Count == 1)
            {
                //指定分厂
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
                sql += string.Format(" AND csr.workcentername = '{0}'", paramsName);
            }
            else if (selectOrg.Count == 2)
            {
                //指定：分厂/工区
                sql += string.Format(" AND csr.factoryid = '{0}' AND csr.workcenterid = '{1}'", selectOrg[0].ToString(), selectOrg[1].ToString());
                sql += string.Format(" AND csr.teamname = '{0}'", paramsName);
            }
            else
            {
                //指定：分厂/工区/班组
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
                sql += string.Format(" AND csr.workcenterid = '{0}'", selectOrg[1].ToString());
                sql += string.Format(" AND csr.teamid = '{0}'", selectOrg[2].ToString());
            }
        }
        else
        {
            sql += string.Format(" AND csp.factoryname = '{0}'", paramsName);
        }

        //计划完成时间
        if (jObject["plannedfinishdate"] != null && jObject["plannedfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedfinishdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) >= to_date('{0}','yyyy/mm/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) <= to_date('{0}','yyyy/mm/dd')", arr[1].ToString());
        }

        //完工时间
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND csp.checkdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND csp.checkdate <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", arr[1].ToString());
        }
        return sql;
    }

    //柱状图明细
    Result getBarChartDetail(JObject jObject)
    {
        string sql = getBarChartDetailSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //折线图明细公用sql
    string getLineChartDetailSql(JObject jObject)
    {
        string sql = @"
SELECT csp.factoryname,csr.workcentername,csr.teamname,csr.reportemployeename,csp.containername,csp.workflowstepname,csp.plannedfinishdate,csp.checkdate,
       TRUNC(csp.checkdate) - TRUNC(csp.plannedfinishdate) delaydays,
       DECODE(csp.status,1,'按时完成',2,'超期完成') status,cp.stepplancreatedate
FROM r_containerstepplanboard@mesreport csp
INNER JOIN containerstepplan cp ON cp.containerid = csp.containerid AND cp.workflowstepname = csp.workflowstepname AND cp.plantype = 1
INNER JOIN containersteprecord csr ON csr.containerid = csp.containerid AND csr.workflowstepname = csp.workflowstepname
WHERE csp.status > 0 AND csr.teamid IS NOT NULL AND csr.reportemployeename IS NOT NULL AND csp.factorycode <> '602'";
        string paramsName = jObject["paramsName"].ToString();
        if (paramsName.Contains("月"))
        {
            string year = paramsName.Split('年')[0];
            string month = paramsName.Split('年')[1].Replace("月", "");
            sql += string.Format(" AND to_char(csp.checkdate,'yyyy') = '{0}'", year);
            sql += string.Format(" AND to_char(csp.checkdate,'mm') = '{0}'", month);
        }
        else if (paramsName.Contains("周"))
        {
            string year = paramsName.Split('年')[0];
            string week = paramsName.Split('年')[1].Replace("周", "");
            sql += string.Format(" AND to_char(csp.checkdate,'yyyy') = '{0}'", year);
            sql += string.Format(" AND to_char(csp.checkdate,'ww') = '{0}'", week);
        }
        else
        {
            sql += string.Format(" AND TRUNC(csp.checkdate) = to_date('{0}','yyyy/mm/dd')", paramsName);
        }

        //所选组织
        if (jObject["selectOrg"] != null && jObject["selectOrg"].ToString() != "[]")
        {
            JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
            if (selectOrg.Count == 1)
            {
                //指定分厂
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
            }
            else if (selectOrg.Count == 2)
            {
                //指定：分厂/工区
                sql += string.Format(" AND csr.factoryid = '{0}' AND csr.workcenterid = '{1}'", selectOrg[0].ToString(), selectOrg[1].ToString());
            }
            else
            {
                //指定：分厂/工区/班组
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
                sql += string.Format(" AND csr.workcenterid = '{0}'", selectOrg[1].ToString());
                sql += string.Format(" AND csr.teamid = '{0}'", selectOrg[2].ToString());
            }
        }

        //计划完成时间
        if (jObject["plannedfinishdate"] != null && jObject["plannedfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedfinishdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) >= to_date('{0}','yyyy/mm/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) <= to_date('{0}','yyyy/mm/dd')", arr[1].ToString());
        }

        //完工时间
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND csp.checkdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND csp.checkdate <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", arr[1].ToString());
        }
        return sql;
    }

    //折线图明细
    Result getLineChartDetail(JObject jObject)
    {
        string sql = getLineChartDetailSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //工人折线图明细公用sql
    string getWorkerTrendDetailSql(JObject jObject)
    {
        string sql = @"
SELECT csp.factoryname,csr.workcentername,csr.teamname,csr.reportemployeename,csp.containername,csp.workflowstepname,csp.plannedfinishdate,csp.checkdate,
       TRUNC(csp.checkdate) - TRUNC(csp.plannedfinishdate) delaydays,
       DECODE(csp.status,1,'按时完成',2,'超期完成') status,cp.stepplancreatedate
FROM r_containerstepplanboard@mesreport csp
INNER JOIN containerstepplan cp ON cp.containerid = csp.containerid AND cp.workflowstepname = csp.workflowstepname AND cp.plantype = 1
INNER JOIN containersteprecord csr ON csr.containerid = csp.containerid AND csr.workflowstepname = csp.workflowstepname
WHERE csp.status > 0 AND csr.teamid IS NOT NULL AND csr.reportemployeename IS NOT NULL AND csp.factorycode <> '602'";
        string paramsName = jObject["paramsName"].ToString();
        if (paramsName.Contains("月"))
        {
            string year = paramsName.Split('年')[0];
            string month = paramsName.Split('年')[1].Replace("月", "");
            sql += string.Format(" AND to_char(csp.checkdate,'yyyy') = '{0}'", year);
            sql += string.Format(" AND to_char(csp.checkdate,'mm') = '{0}'", month);
        }
        else if (paramsName.Contains("周"))
        {
            string year = paramsName.Split('年')[0];
            string week = paramsName.Split('年')[1].Replace("周", "");
            sql += string.Format(" AND to_char(csp.checkdate,'yyyy') = '{0}'", year);
            sql += string.Format(" AND to_char(csp.checkdate,'ww') = '{0}'", week);
        }
        else
        {
            sql += string.Format(" AND TRUNC(csp.checkdate) = to_date('{0}','yyyy/mm/dd')", paramsName);
        }

        //工人
        if (jObject["worker"] != null && jObject["worker"].ToString() != "")
        {
            sql += string.Format(" AND csr.reportemployeename = '{0}'", jObject["worker"].ToString());
        }

        //所选组织
        if (jObject["selectOrg"] != null && jObject["selectOrg"].ToString() != "[]")
        {
            JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
            if (selectOrg.Count == 1)
            {
                //指定分厂
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
            }
            else if (selectOrg.Count == 2)
            {
                //指定：分厂/工区
                sql += string.Format(" AND csr.factoryid = '{0}' AND csr.workcenterid = '{1}'", selectOrg[0].ToString(), selectOrg[1].ToString());
            }
            else
            {
                //指定：分厂/工区/班组
                sql += string.Format(" AND csr.factoryid = '{0}'", selectOrg[0].ToString());
                sql += string.Format(" AND csr.workcenterid = '{0}'", selectOrg[1].ToString());
                sql += string.Format(" AND csr.teamid = '{0}'", selectOrg[2].ToString());
            }
        }

        //计划完成时间
        if (jObject["plannedfinishdate"] != null && jObject["plannedfinishdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["plannedfinishdate"].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) >= to_date('{0}','yyyy/mm/dd')", arr[0].ToString());
            sql += string.Format(" AND TRUNC(csp.plannedfinishdate) <= to_date('{0}','yyyy/mm/dd')", arr[1].ToString());
        }

        //完工时间
        if (jObject["checkdate"] != null && jObject["checkdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["checkdate"].ToString());
            sql += string.Format(" AND csp.checkdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')", arr[0].ToString());
            sql += string.Format(" AND csp.checkdate <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')", arr[1].ToString());
        }

        return sql;
    }

    //工人折线图明细
    Result getWorkerTrendDetail(JObject jObject)
    {
        string sql = getWorkerTrendDetailSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //柱状图明细导出
    void exportBarChartDetail(JObject jObject)
    {
        string sql = getBarChartDetailSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["factoryname"].Caption = "分厂";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["teamname"].Caption = "班组";
        dtResult.Columns["reportemployeename"].Caption = "报工人";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["workflowstepname"].Caption = "工序";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["checkdate"].Caption = "完工时间";
        dtResult.Columns["delaydays"].Caption = "完成时间差";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["stepplancreatedate"].Caption = "排产时间";
        ExportHelper.ExportExcel(dtResult, jObject["paramsName"].ToString() + "明细");
    }

    //折线图明细导出
    void exportLineChartDetail(JObject jObject)
    {
        string sql = getLineChartDetailSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["factoryname"].Caption = "分厂";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["teamname"].Caption = "班组";
        dtResult.Columns["reportemployeename"].Caption = "报工人";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["workflowstepname"].Caption = "工序";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["checkdate"].Caption = "完工时间";
        dtResult.Columns["delaydays"].Caption = "完成时间差";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["stepplancreatedate"].Caption = "排产时间";
        ExportHelper.ExportExcel(dtResult, jObject["paramsName"].ToString() + "明细");
    }

    //工人折线图明细导出
    void exportWorkerLineChartDetail(JObject jObject)
    {
        string sql = getWorkerTrendDetailSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["factoryname"].Caption = "分厂";
        dtResult.Columns["workcentername"].Caption = "工区";
        dtResult.Columns["teamname"].Caption = "班组";
        dtResult.Columns["reportemployeename"].Caption = "报工人";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["workflowstepname"].Caption = "工序";
        dtResult.Columns["plannedfinishdate"].Caption = "计划完成时间";
        dtResult.Columns["checkdate"].Caption = "完工时间";
        dtResult.Columns["delaydays"].Caption = "完成时间差";
        dtResult.Columns["status"].Caption = "状态";
        dtResult.Columns["stepplancreatedate"].Caption = "排产时间";
        ExportHelper.ExportExcel(dtResult, jObject["paramsName"].ToString() + "明细");
    }
}